/****** Script for SelectTopNRows command from SSMS  ******/
--USE IRCTC

SELECT * FROM [VOTERLIST];

  select * from VOTERLIST where aadhar in (1111,2222);
  update VOTERLIST set PAN = null  where aadhar in (1111,2222);

  --isnull() function --> If a column value is null, then it will display given message in place of NULL value.
  --isnull(ColumnName, MessageToBeDisplayedInCaseOfNULLValue)

  --isnull(PAN, 'PAN NUMBER UNAVAILABLE')
	
select AADHAR, name, age, PINCODE, dob, pan,CitizenType, Gender, created_on from VOTERLIST	

select AADHAR, name, age, PINCODE, dob, isnull(pan,'No PAN'),CitizenType, Gender, created_on from VOTERLIST	

select pan, isnull(pan,'No PAN') from VOTERLIST;



select AADHAR, name, age, PINCODE, dob, pan,CitizenType, Gender, created_on from VOTERLIST;	

select age, IIF(age <= 35, 'YoungVoter','MiddleAgeVoter') as VoterType from VOTERLIST

select AADHAR, name, age, IIF(age <= 35, 'YoungVoter','MiddleAgeVoter'), PINCODE, dob, pan,CitizenType, Gender, created_on from VOTERLIST;	

--IIF(Condition, TRUEValue, FALSEValue)
--Condition --> age<= 35
--If condition is TRUE then TRUEValue will be dispalyed in output	-->	YoungVoter
--If condition is FALSE then FALSEValue will be dispalyed in output	-->	MiddleAgeVoter

--Nested IIF condition --> IIF condition used inside another IIF condition
-->	IIF(age <= 35, 'YoungVoter', iif(age>=60,'SeniorCitizenVoter','MiddleAgeVoter') )

select 
		AADHAR, 
		name, 
		age, 
		IIF(age <= 35, 'YoungVoter','MiddleAgeVoter'), 
		IIF(age <= 35, 'YoungVoter', iif(age>=60,'SeniorCitizenVoter','MiddleAgeVoter') ),
		PINCODE, 
		dob, 
		pan,
		isnull(PAN,'No PAN'),
		CitizenType, 
		Gender,
		created_on 
	from VOTERLIST;	


--Concatenation using + operator --> Combines different values into a singe value, if any of the value is NULL then our entire result will be NULL.

select 10 +15	-->	25

select convert(varchar,10) + 'SQL'	-->	10SQL

select 'SQL' + 'Training'	-->	SQLTraining
	
select 'SQL' + ' ' + 'Training'	--> SQL Training

select 'SQL' + ' ' + 'Training' + null	--> Returns NULL

select 'SQL' + ' ' + 'Training' + null	+ 'Program'	--> Returns NULL


--Concatenation using concat method --> concat method combines given parameters into a single value. It ignores null values.
select concat('SQL','Training')	--> SQLTraining

select concat('SQL',' ','Training')	--> SQL Training

select concat('SQL',' ','Training', null)	--> SQL Training

select concat('SQL',' ','Training', null, 'Program')	--> SQL TrainingProgram


select 
		AADHAR, 
		name, 
		age
		PINCODE, 
		pan,
		name + pan  as ConcatUsingPlusOperator,
		concat( name , pan ) as ConcatUsingConcatFunction
	from VOTERLIST;